from db_init import db
from flask_login import UserMixin
import sqlalchemy as sq
from sqlalchemy.dialects.mysql import LONGTEXT

# 用户收藏课程关系表
user_collect_course = db.Table('user_collect_course',
                               db.Column('course_id', db.Integer, db.ForeignKey('course.id'), primary_key=True),
                               db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True)
                               )
# 用户收藏训练营关系表
user_collect_tcourse = db.Table('user_collect_tcourse',
                                db.Column('tcourse_id', db.Integer, db.ForeignKey('tcourse.id'), primary_key=True),
                                db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True)
                                )

# 用户观看章节关系表
user_course_chapter = db.Table('user_course_chapter',
                                db.Column('chapter_id', db.Integer, db.ForeignKey('chapter.id'), primary_key=True),
                                db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True),
                                )

# 用户观看课程的进度
user_tcourse_course = db.Table('user_tcourse_course',
                               db.Column('chapter_id', db.Integer, db.ForeignKey('course.id'), primary_key=True),
                               db.Column('tcourse_id', db.Integer, db.ForeignKey('tcourse.id'), primary_key=True),
                               db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True)
                               )
# 训练营和课程关系表
course_collect_tcourse = db.Table('course_collect_tcourse',
                                  db.Column('tcourse_id', db.Integer, db.ForeignKey('tcourse.id'), primary_key=True),
                                  db.Column('course_id', db.Integer, db.ForeignKey('course.id'), primary_key=True)
                                  )
# 角色权限
role_power = db.Table('role_power',
                      db.Column('role_id', db.Integer, db.ForeignKey('role.id'), primary_key=True),
                      db.Column('power_id', db.Integer, db.ForeignKey('power.id'), primary_key=True)
                      )
# 角色用户
role_user = db.Table('role_user',
                     db.Column('role_id', db.Integer, db.ForeignKey('role.id'), primary_key=True),
                     db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True),
                     db.Column('end_t',db.Integer)

                     )

# 课程标签
course_tag = db.Table('course_tag',
                      db.Column('course_id', db.Integer, db.ForeignKey('course.id'), primary_key=True),
                      db.Column('tag_id', db.Integer, db.ForeignKey('tags.id'), primary_key=True)
                      )


class BaseModel(db.Model):
    __abstract__ = True
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)

# 用户表
class User(BaseModel, UserMixin):
    __tablename__ = "user"
    nikename = db.Column(db.String(100), nullable=False, comment="姓名")
    mobile = db.Column(db.String(11), unique=True, comment="手机号")
    password = db.Column(db.String(200), comment="密码")
    url = db.Column(db.String(255), comment="头像")
    vip = db.Column(db.Boolean,comment='vip状态')
    # 用户的具体时间
    end_t = db.Column(db.Integer,comment='vip过期时间')
    # 金额豆
    bean = db.Column(db.Integer,comment='实验豆',nullable=False)
    # 老师的课程
    courses = db.relationship("Course", backref="teacher")
    # 老师的训练营
    tcourse = db.relationship("Tcourse", backref="teacher")
    #与第三方的关系
    broker = db.relationship('BrokerUser',backref='user')
    #章节
    chapters = db.relationship('Chapter',backref='user',secondary=user_course_chapter)
    # 用户收藏
    user_order = db.relationship('Course', backref='user', secondary=user_collect_course)
    #关联评论
    course_comment = db.relationship('CourseComment',backref='author')
    #关联新闻
    news = db.relationship('News',backref='user')


# 第三方的登录表
class BrokerUser(BaseModel, UserMixin):
    __tablename__ = "broker_user"
    broker_name = db.Column(db.Enum('weibo','github','QQ'))
    broker_id = db.Column(db.Integer)
    token = db.Column(db.String(200))
    #关联用户
    broker_uid = db.Column(db.Integer,db.ForeignKey('user.id'))

# 角色表
class Role(BaseModel):
    __tablename__ = "role"
    name = db.Column(db.String(32), nullable=False, comment="角色名")
    zhe = sq.Column(sq.FLOAT, server_default=sq.text('1'), comment="打折幅度")
    end_t = db.Column(sq.Integer, default=0, comment="权限结束时间")
    user = db.relationship('User',backref='role',secondary=role_user)
    price = db.Column(db.FLOAT,comment='会员购买价格')

# 权限表
class Power(BaseModel):
    __tablename__ = "power"
    name = db.Column(db.String(32), nullable=False, comment="描述")
    url = db.Column(db.String(32), nullable=False, comment="url")
    msg = db.Column(db.String(32),nullable=False,comment='内容')
    method = db.Column(db.String(32),nullable=False,comment='请求类型')
    user = db.relationship('Role', backref='power', secondary=role_power)

#课程表
class Course(BaseModel):
    __tablename__ = "course"
    name = db.Column(db.String(32), nullable=False, comment="课程名字")
    img = db.Column(db.String(1024), nullable=False, comment="图片")
    look_num = sq.Column(sq.Integer, nullable=False, comment="浏览次数")
    collect_num = sq.Column(sq.Integer, nullable=False, comment="收藏次数")
    online_status = sq.Column(sq.Boolean, nullable=False, comment="是否上线")
    online_t = sq.Column(sq.Integer, nullable=False, comment="上线时间")
    vip_status = sq.Column(sq.Boolean, nullable=False, comment="是否会员")
    tcoures_status = sq.Column(sq.Boolean, nullable=False, comment="是否训练营")
    teacher_id = sq.Column(sq.Integer, sq.ForeignKey('user.id'))
    # 分类 枚举（免费 限免 会员）
    cate = db.Column(db.Enum('免费', '会员', '限免'))
    # 反查 章节
    chapter_id = db.relationship('Chapter',backref='course')
    # 反查 评论
    comment_id = db.relationship('CourseComment',backref='course')
    # 反查 训练营 推荐我的 训练营都有哪些
    recommend = db.relationship('Tcourse', backref='course')
    #排序
    sort_num = db.Column(db.Integer)
    #和新闻关联
    news_id = db.relationship('News',backref='course')

# 章节表
class Chapter(BaseModel):
    __tablename__ = "chapter"
    course_id = sq.Column(sq.Integer, sq.ForeignKey('course.id'))
    title = db.Column(db.String(32))
    info = db.Column(db.String(256))
    duration = sq.Column(sq.Integer, comment='时长 秒')
    path = sq.Column(sq.String(1024), comment='视频地址')

# 课程评论表
class CourseComment(db.Model):
    __tablename__ = "course_comment"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    course_id = sq.Column(sq.Integer, sq.ForeignKey('course.id'))
    msg = db.Column(db.String(256))
    user = sq.Column(sq.Integer, sq.ForeignKey('user.id'))
    reply_id = sq.Column(sq.Integer, sq.ForeignKey('course_comment.id'))
    root_id = db.Column(db.Integer)
    reply = db.relationship("CourseComment", remote_side=[id], backref='beau_id')

# 训练营篇评论表
class TcourseComment(db.Model):
    __tablename__ = "tcourse_comment"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    course = sq.Column(sq.Integer, sq.ForeignKey('tcourse.id'))
    msg = db.Column(db.String(256))
    user = sq.Column(sq.Integer, sq.ForeignKey('user.id'))
    reply_id = sq.Column(sq.Integer, sq.ForeignKey('tcourse_comment.id'))
    # reply = db.relationship("CourseComment", remote_side=[id], backref='beau_id')

# 训练营图片表
class TcourseImg(db.Model):
    __tablename__ = "tcourse_img"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    tcourse = sq.Column(sq.Integer, sq.ForeignKey('tcourse.id'))
    name = db.Column(db.String(256))
    url = db.Column(db.String(1024))

# 训练营课程表
class Tcourse(BaseModel):
    __tablename__ = "tcourse"
    title = db.Column(db.String(30))
    url = db.Column(db.String(1024))
    price = db.Column(db.Float)
    online = sq.Column(sq.Boolean, server_default=sq.text('0'), comment='上线状态')
    online_t = sq.Column(sq.Integer, comment='上线时间')
    intro = db.Column(db.Text)
    collect_num = db.Column(sq.Integer, server_default=sq.text('0'), comment='收藏数量')
    recommend = sq.Column(sq.Integer, sq.ForeignKey('course.id'), comment='训练营推荐课程')
    # 训练营包含的课程
    courses = db.relationship('Course', backref='tcourse', secondary=course_collect_tcourse)
    teacher_id = sq.Column(sq.Integer, sq.ForeignKey('user.id'))
    imgs = db.relationship('TcourseImg',backref='tcourses')

# 分类
class Tags(BaseModel):
    __tablename__ = "tags"
    courses = db.relationship('Course', backref='tags', secondary=course_tag)
    name = db.Column(db.String(30))

#订单
class Order(BaseModel):
    __tablename__ = "order"
    user = sq.Column(sq.Integer, sq.ForeignKey('user.id'))
    order_type = sq.Column(sq.Enum("vip", "课程", "训练营"))
    course = sq.Column(sq.Integer, sq.ForeignKey('course.id'))
    role = sq.Column(sq.Integer, sq.ForeignKey('role.id'))
    tcourse = sq.Column(sq.Integer, sq.ForeignKey('tcourse.id'))
    end_t = sq.Column(sq.Integer)

class News(BaseModel):
    __tablename__ = "news"
    types_column = ('交流讨论','站内内容','问题讨论')
    title = db.Column(db.String(24))
    # 创建时间
    c_time = sq.Column(sq.Integer)
    #绑定的课程
    course_id = db.Column(db.Integer,db.ForeignKey('course.id'))
    countent = db.Column(sq.dialects.mysql.LONGTEXT,comment='内容')
    title_img = db.Column(db.String(1024),comment='标题图片')
    read_num = db.Column(db.Integer)
    top = db.Column(db.Boolean,comment='是否置顶')
    user_id = db.Column(db.Integer,db.ForeignKey('user.id'))
    comment_num = db.Column(db.Integer,comment='评论量',default=0)
    news_type = sq.Column(sq.Enum(*types_column))

class ChatHistory(BaseModel):
    __tablename__ = "chat_history"
    content = db.Column(db.String(1024),comment='聊天内容')
    c_time = db.Column(db.Integer,comment='聊天时间')
    user_id = db.Column(db.Integer,db.ForeignKey('user.id'),comment='评论发送者')
    to_room = db.Column(db.Integer,db.ForeignKey('room.id'),comment='群')

user_room = db.Table(
    'user_room',
    db.Column('room_id', db.Integer, db.ForeignKey('room.id'), primary_key=True),
    db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True),
    db.Column('chat_history_id', db.Integer),
)

class Room(BaseModel):
    __tablename__ = "room"
    room_code = db.Column(db.String(32),comment='群唯一表示码',unique=True)
    room_name = db.Column(db.String(32),comment='群名')
    users = db.relationship('User', backref='rooms', secondary=user_room)
    history = db.relationship('ChatHistory', backref='rooms')


